package com.manager.plat.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.IConfigRouterDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.ConfigRouterVo;
/**
 * 渠道路由数据实现层
 * @author liugeng
 *
 */
public class ConfigRouterDaoImpl extends JdbcDaoSupport implements IConfigRouterDao {
	private static Logger logger = Logger.getLogger(ConfigRouterDaoImpl.class);
	/**获得所有渠道路由配置信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<ConfigRouterVo> getConfigRouterList() {
		String sql = "select id, name, sp_code_id, sp_number, is_self_consum, detail, sync_url, lower_id, start_second, end_second from tb_ivr_config_router where status = 1";
		logger.info(String.format("获得所有渠道路由配置信息列表[%s]", sql));
		
		List<ConfigRouterVo> configRouterVoList = this.getJdbcTemplate().query(sql, rowMapper);
		if(configRouterVoList != null && configRouterVoList.size() > 0){
			return configRouterVoList;
		}
		
		return null;
	}
	/**根据Id获得渠道路由配置信息*/
	@SuppressWarnings("unchecked")
	@Override
	public ConfigRouterVo getConfigRouterById(int id) {
		String sql = "select id, name, sp_code_id, sp_number, is_self_consum, detail, sync_url, lower_id, start_second, end_second from tb_ivr_config_router where id = ? and status = 1";
		Object[] param = new Object[]{id};
		logger.info(String.format("根据Id获得渠道路由配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		List<ConfigRouterVo> configRouterVoList = this.getJdbcTemplate().query(sql, param, rowMapper);
		if(configRouterVoList != null && configRouterVoList.size() > 0){
			return configRouterVoList.get(0);
		}
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<ConfigRouterVo> rowMapper = new ParameterizedRowMapper<ConfigRouterVo>() {
		@Override
		public ConfigRouterVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			ConfigRouterVo configRouterVo = new ConfigRouterVo();
			configRouterVo.setId(rs.getInt("id"));
			configRouterVo.setName(rs.getString("name"));
			configRouterVo.setSp_code_id(rs.getInt("sp_code_id"));
			configRouterVo.setSp_number(rs.getString("sp_number"));
			configRouterVo.setIs_self_consum(rs.getInt("is_self_consum"));
			configRouterVo.setDetail(rs.getString("detail"));
			configRouterVo.setSync_url(rs.getString("sync_url"));
			configRouterVo.setLower_id(rs.getInt("lower_id"));
			configRouterVo.setStart_second(rs.getInt("start_second"));
			configRouterVo.setEnd_second(rs.getInt("end_second"));
			return configRouterVo;
		}
	};
	/**新增渠道路由配置信息*/
	@Override
	public int insertConfigRouter(ConfigRouterVo configRouterVo) {
		String sql = "insert tb_ivr_config_router(name, sp_code_id, sp_number, is_self_consum, detail, sync_url, lower_id, start_second, end_second, status, add_user) values(?,?,?,?,?,?,?,?,?,1,?)";
		Object[] param = new Object[]{configRouterVo.getName(), configRouterVo.getSp_code_id(),
									configRouterVo.getSp_number(), configRouterVo.getIs_self_consum(),
									configRouterVo.getDetail(), configRouterVo.getSync_url(),
									configRouterVo.getLower_id(), configRouterVo.getStart_second(),
									configRouterVo.getEnd_second(), configRouterVo.getAdd_user()};
		
		logger.info(String.format("新增渠道路由配置信息[%s]", BaseUtil.logSQL(sql, param)));
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**更新渠道路由配置信息*/
	@Override
	public int updateConfigRouter(ConfigRouterVo configRouterVo) {
		String sql = "update tb_ivr_config_router set name = ?, sp_code_id = ?, sp_number = ?, is_self_consum = ?, detail = ?, sync_url = ?, lower_id = ?, start_second = ?, end_second = ?, update_user = ?, update_date = now() where id = ?";
		Object[] param = new Object[]{configRouterVo.getName(), configRouterVo.getSp_code_id(),
									configRouterVo.getSp_number(), configRouterVo.getIs_self_consum(),
									configRouterVo.getDetail(), configRouterVo.getSync_url(),
									configRouterVo.getLower_id(), configRouterVo.getStart_second(),
									configRouterVo.getEnd_second(), configRouterVo.getUpdate_user(),
									configRouterVo.getId()};
		logger.info(String.format("更新渠道路由配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**删除渠道路由配置信息*/
	@Override
	public int deleteConfigRouter(int id, int update_user) {
		String sql = "update tb_ivr_config_router set status = 0, update_user = ?, update_date = now() where id = ?";
		Object[] param = new Object[]{update_user, id};
		logger.info(String.format("删除渠道路由配置信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**查询长号码信息列表包含被删除的*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getSpnumberList() {
		String sql = "select sp_number from tb_ivr_config_router where status = 1 group by sp_number";
		logger.info(String.format("查询长号码信息列表包含被删除的[%s]", sql));
		
		List<String> spnumberList = this.getJdbcTemplate().query(sql, tempMapper);
		if(spnumberList != null && spnumberList.size() > 0){
			return spnumberList;
		}
		
		return null;
	}
	/**查询渠道长号码信息列表根据渠道ID*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getSpnumberPartnerList(int lower_id) {
		String sql = "select sp_number from tb_ivr_config_router cr where cr.lower_id = ? and cr.status = 1 group by sp_number";
		Object[] param = new Object[]{lower_id};
		logger.info(String.format("查询渠道长号码信息列表根据渠道ID[%s]", BaseUtil.logSQL(sql, param)));
		
		List<String> spnumberList = this.getJdbcTemplate().query(sql, param, tempMapper);
		if(spnumberList != null && spnumberList.size() > 0){
			return spnumberList;
		}
		
		return null;
	}
	/**查询渠道长号码信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getSpnumberListLowerByUserId(int userId) {
		String sql = "select sp_number from tb_ivr_config_router cr " +
					 "inner join tb_ivr_config_lower_partner clp on clp.id = cr.lower_id " +
					 "where clp.user_id = ? and cr.status = 1 " +
					 "group by sp_number";
		Object[] param = new Object[]{userId};
		logger.info(String.format("查询渠道长号码信息列表[%s]", BaseUtil.logSQL(sql, param)));
		
		List<String> spnumberList = this.getJdbcTemplate().query(sql, param, tempMapper);
		if(spnumberList != null && spnumberList.size() > 0){
			return spnumberList;
		}
		
		return null;
	}
	/**查询自消费的长号码信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getSpnumberConsumList() {
		String sql = "select sp_number from tb_ivr_config_router where is_self_consum = 1 and status = 1 group by sp_number";
		logger.info(String.format("查询自消费的长号码信息列表[%s]", sql));
		
		List<String> spnumberList = this.getJdbcTemplate().query(sql, tempMapper);
		if(spnumberList != null && spnumberList.size() > 0){
			return spnumberList;
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<String> tempMapper = new ParameterizedRowMapper<String>() {
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			String sp_number = rs.getString("sp_number");
			return sp_number;
		}
	};
}
